﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BackEnd
{
	public class SQLiteManager
	{
		#region EVENTS
		public event EventHandler<Result> Result;
		public event EventHandler<ErrorEventArgs> Error;
		public event EventHandler SchemaChanged;
		public event EventHandler<Database> Connected;
		public event EventHandler<Database> Attached;
		public event EventHandler<string> Detached;
		public event EventHandler Disconnected;
		#endregion

		#region PROPERTIES
		public Database MainDatabase
		{
			get
			{
				return Databases.FirstOrDefault(db => db.Type == DatabaseType.Main);
			}
		}
		public List<Database> Databases { get; private set; }
		#endregion

		#region PRIVATE VARIABLES
		private string _connStr;
		private SQLiteConnection _conn;
		#endregion

		#region CONSTRUCTOR(S)
		public SQLiteManager()
		{
			this.Databases = new List<Database>();
		}
		#endregion

		#region PUBLIC METHODS

		public void Create(string filePath)
		{
			try
			{
				SQLiteConnection.CreateFile(filePath);
				Connect(filePath);
			}
			catch (Exception ex)
			{
				OnError(new ErrorEventArgs(ex));
			}
		}
		public void Connect(string dbPath)
		{
			if (!string.IsNullOrEmpty(dbPath) && File.Exists(dbPath))
			{
				// if a db is already open then close it first
				var isMainExists = this.Databases.Any(x => x.Type == DatabaseType.Main);
				if(isMainExists)
				{
					Disconnect();
				}

				// connect
				try
				{
					_connStr = string.Format("Data Source={0};Version=3;Pooling=True;Max Pool Size=100;", dbPath);
					_conn = new SQLiteConnection(_connStr);
					_conn.Open();
					Database db = new Database("main", dbPath, DatabaseType.Main); // add as main
					FillSchema(db);
					this.Databases.Add(db);
					OnConnected(db);
				}
				catch (Exception ex)
				{
					OnError(new ErrorEventArgs(ex));
				}
			}
		}
		public void Attach(string filePath)
		{
			if (!string.IsNullOrEmpty(filePath) && File.Exists(filePath))
			{
				var alreadyOpen = this.Databases.Any(x => x.Path == filePath);
				if (!alreadyOpen)
				{
					var dbName = Path.GetFileNameWithoutExtension(filePath);
					try
					{
						var sql = string.Format("ATTACH DATABASE '{0}' as '{1}';", filePath, dbName);
						new SQLiteCommand(sql, _conn).ExecuteNonQuery();
						var db = new Database(dbName, filePath, DatabaseType.Attached);
						FillSchema(db);
						this.Databases.Add(db);
						OnAttached(db);
					}
					catch (Exception ex)
					{
						OnError(new ErrorEventArgs(ex));
					}
				}
			}
		}
		public void Detach(string dbName)
		{
			var db = this.Databases.FirstOrDefault(x => x.Name == dbName);
			if (db != null && db.Type == DatabaseType.Attached)
			{
				var sql = string.Format("DETACH DATABASE '{0}';", db.Name);
				try
				{
					new SQLiteCommand(sql, _conn).ExecuteNonQuery();
					this.Databases.Remove(db);
					OnDetached(dbName);
				}
				catch (Exception ex)
				{
					OnError(new ErrorEventArgs(ex));
				}
			}
		}
		public void Disconnect()
		{
			_conn.Close();
			this.Databases.Clear();
			OnDisconnected();
		}

		public void HandleRequest(DataRequest request)
		{
			try
			{
				var sw = new Stopwatch();
				var ds = new DataSet();

				using (var cmd = new SQLiteCommand(request.SQL, _conn))
				{
					using (var adp = new SQLiteDataAdapter(cmd))
					{
						sw.Start();
						adp.Fill(ds);
						sw.Stop();
					}
				}

				OnResult(new VectorResult(ds.Tables[0], sw.Elapsed, request));
			}
			catch (Exception ex)
			{
				OnError(new ErrorEventArgs(ex));
			}
		}
		public void HandleRequest(ScriptRequest request)
		{
			if (request.Type == ScriptType.Create && request.Target is ICreatable)
			{
				var createSQL = ((ICreatable)request.Target).CreateSQL;
				OnResult(new ScriptResult(createSQL, new TimeSpan(0), request));
			}
		}
		public void HandleRequest(TaskRequest request)
		{
			try
			{
				if (request.Type == TaskType.CheckIntegrity)
				{
					using (var cmd = new SQLiteCommand("PRAGMA integrity_check", _conn))
					{
						object value = cmd.ExecuteScalar();
						OnResult(new ScalarResult(value, new TimeSpan(0), request));
					}				
				}
				else if (request.Type == TaskType.Vacuum)
				{
					using (var cmd = new SQLiteCommand("VACUUM", _conn))
					{
						long sizeBeforeVac = GetFileSize(MainDatabase.Path);
						cmd.ExecuteScalar();
						long sizeAfterVac = GetFileSize(MainDatabase.Path);
						var msg = string.Format("Space saved: {0} bytes", sizeBeforeVac - sizeAfterVac);
						OnResult(new ScalarResult(msg, new TimeSpan(0), request));
					}
				}
			}
			catch (Exception ex)
			{
				OnError(new ErrorEventArgs(ex));
			}
		}

		private static long GetFileSize(string filePath)
		{
			return new FileInfo(filePath).Length;
		}

		protected virtual void OnResult(Result result)
		{
			if (Result != null)
				Result(this, result);
		}
		protected virtual void OnError(ErrorEventArgs error)
		{
			if (Error != null)
				Error(this, error);
		}
		protected virtual void OnConnected(Database db)
		{
			if (Connected != null)
				Connected(this, db);
		}
		protected virtual void OnDisconnected()
		{
			if (Disconnected != null)
				Disconnected(this, EventArgs.Empty);
		}
		protected virtual void OnAttached(Database db)
		{
			if (Attached != null)
				Attached(this, db);
		}
		protected virtual void OnDetached(string dbName)
		{
			if (Detached != null)
				Detached(this, dbName);
		}
		#endregion

		#region PRIVATE METHODS
		private void FillSchema(Database db)
		{
			var sql = string.Format("SELECT type, name, tbl_name, sql FROM [{0}].sqlite_master", db.Name);
			var cmd = new SQLiteCommand(sql, _conn);
			var rdr = cmd.ExecuteReader();
			while (rdr.Read())
			{
				var type = rdr.GetString(0);
				var name = rdr.GetString(1);
				var createSQL = rdr.GetValue(3).ToString();

				if (type == "table")
				{
					var tbl = new TableView(name, createSQL, false);
					PopulateColumns(tbl);
					db.Schema.Tables.Add(tbl);
				}
				else if (type == "view")
				{
					var vw = new TableView(name, createSQL, true);
					PopulateColumns(vw);
					db.Schema.Views.Add(vw);
				}
				else
				{
					var parentName = rdr.GetString(2);
					var parent = db.Schema.Tables.SingleOrDefault(x => x.Name == parentName) ??
								 db.Schema.Views.SingleOrDefault(x => x.Name == parentName);

					if (type == "index")
					{
						parent.Indexes.Add(new Index(name, createSQL));
					}
					else if (type == "trigger")
					{
						parent.Triggers.Add(new Trigger(name, createSQL));
					}
				}
			}
		}
		private void PopulateColumns(TableView tableOrView)
		{
			var cmd = new SQLiteCommand(string.Format("PRAGMA table_info([{0}])", tableOrView.Name), _conn);
			var rdr = cmd.ExecuteReader();
			while (rdr.Read())
			{
				var id = rdr.GetInt32(0);
				var name = rdr.GetString(1);
				var dataType = rdr.GetString(2);
				var col = new Column(name, dataType);
				tableOrView.Columns.Add(col);
			}
		}
		#endregion
	}
}
